package com.icexxx.icemock.util;

import java.io.File;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.icexxx.icemock.pojo.Feather;
import com.icexxx.icemock.pojo.Peafowl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.db.meta.Column;
import cn.hutool.db.meta.MetaUtil;
import cn.hutool.db.meta.Table;
import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.setting.Setting;

public class DatabaseBattery {
	private static Connection connection = null;
	private static Boolean flag = null;
	public static void executeSql(List<String> insertSqlList) {
		DataSource dataSource = initDb();
		Connection connection = null;
		if(DatabaseBattery.connection == null) {
			try {
				connection = dataSource.getConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DatabaseBattery.connection = connection;
		}else {
			connection = DatabaseBattery.connection;
		}
		int width = 1000;
		if (CollUtil.isNotEmpty(insertSqlList)) {
			int size = insertSqlList.size();
			int group = size / width;
			try {
				for (int i = 0; i < group; i++) {
					List<String> sub = CollUtil.sub(insertSqlList, i * width, (i + 1) * width);
					SqlExecutor.executeBatch(connection, sub);
				}
				List<String> sub = CollUtil.sub(insertSqlList, group * width, size);
				SqlExecutor.executeBatch(connection, sub);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if (connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}

	}

	public static String convertSql(Peafowl peafowl, Integer index) {
		if (peafowl == null) {
			return null;
		}
		String tableName = peafowl.getTableName();
		tableName = StrUtil.trim(tableName);
		List<Feather> featherList = peafowl.getFeather();
		List<Feather> featherNewList = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		StringBuilder valueBuilder = new StringBuilder();
		if (StrUtil.isNotBlank(tableName)) {
			if (CollUtil.isNotEmpty(featherList)) {
				for (Feather feather : featherList) {
					if (feather != null) {
						String name = feather.getName();
						String expression = feather.getExpression();
						String value = null;
						if (StrUtil.isNotBlank(expression)) {
							value = Crystallize.analysis(expression, index);
						}
						Feather featherNew = new Feather();
						featherNew.setName(name);
						featherNew.setExpression(value);
						featherNewList.add(featherNew);
					}
				}
			}
			DataSource dataSource = initDb();
			Connection connection = null;
			if(DatabaseBattery.connection == null) {
				try {
					connection = dataSource.getConnection();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				DatabaseBattery.connection = connection;
			}else {
				connection = DatabaseBattery.connection;
			}
			Table tableMeta = MetaUtil.getTableMeta(dataSource, tableName);
			List<Column> columnList = Convert.toList(Column.class, tableMeta.getColumns());
			Map<String, String> map = new HashMap<>();
			for (Column column : columnList) {
				String name = column.getName();
				String typeName = column.getTypeName();
				String typeFormat = formatType(typeName);
				map.put(name, typeFormat);
			}
			sb.append("insert into ");
			sb.append(tableName);
			sb.append("(");
			if (CollUtil.isNotEmpty(featherNewList)) {
				for (int i = 0; i < featherNewList.size(); i++) {
					Feather feather = featherNewList.get(i);
					if (feather != null) {
						String name = feather.getName();
						String value = feather.getExpression();
						String type = map.get(name);
						if ("str".equals(type)) {
							if (value == null) {
								value = null;
							} else {
								value = StrUtil.wrap(value, "\"");
							}
						}
						sb.append(name);
						valueBuilder.append(value);
						if (i != featherNewList.size() - 1) {
							sb.append(",");
							valueBuilder.append(",");
						}

					}
				}
			}
			sb.append(") values (");
			sb.append(valueBuilder.toString());
			sb.append(")");
		}
		System.out.println("sql=" + sb.toString());
		return sb.toString();
	}

	private static String formatType(String typeName) {
		String columnTypeCast = columnTypeCast(typeName);
		if ("Integer".equals(columnTypeCast)) {
			return "int";
		}
		if ("Long".equals(columnTypeCast)) {
			return "int";
		}
		if ("Double".equals(columnTypeCast)) {
			return "int";
		}
		if ("BigDecimal".equals(columnTypeCast)) {
			return "int";
		}
		return "str";
	}

	public static String columnTypeCast(String columnType) {
		if (columnType == null) {
			return "";
		}
		String type = columnType.trim().toLowerCase();
		if ("varchar".equals(type)) {
			return "String";
		} else if ("int".equals(type)) {
			return "Integer";
		} else if ("number".equals(type)) {
			return "Integer";
		} else if ("integer".equals(type)) {
			return "Integer";
		} else if ("int unsigned".equals(type)) {
			return "Integer";
		} else if ("tinyint".equals(type)) {
			return "Integer";
			// }else if("int".equals(type)){
			// return "int";
		} else if ("varchar2".equals(type)) {
			return "String";
			// }else if("date".equals(type)){
			// return "Date";
		} else if ("date".equals(type)) {
			return "Date";
		} else if ("double".equals(type)) {
			return "Double";
		} else if ("double unsigned".equals(type)) {
			return "Double";
		} else if ("bigint".equals(type)) {
			return "Long";
		} else if ("char".equals(type)) {
			return "String";
		} else if ("nvarchar".equals(type)) {
			return "String";
		} else if ("text".equals(type)) {
			return "String";
		} else if ("".equals(type)) {
			return "String";
		} else if ("datetime".equals(type)) {
			return "Date";
		} else if ("smallint".equals(type)) {
			return "Short";
		} else if ("timestamp".equals(type)) {
			return "Date";
		} else if ("time".equals(type)) {
			return "Date";
		} else if ("decimal".equals(type)) {
			return "BigDecimal";
		} else if ("float".equals(type)) {
			return "Float";
		} else if ("integer unsigned".equals(type)) {
			return "Integer";
		} else if ("bigint unsigned".equals(type)) {
			return "Long";
			// }else if("int unsigned".equals(type)){
			// return "Integer";
		} else if ("decimal unsigned".equals(type)) {
			return "BigDecimal";
		} else if ("tinyblob".equals(type)) {
			return "byte[]";
		} else if ("longblob".equals(type)) {
			return "byte[]";
		} else if ("blob".equals(type)) {
			return "byte[]";
		} else if ("binary".equals(type)) {
			return "byte[]";
		} else if ("varbinary".equals(type)) {
			return "byte[]";
		} else if ("tinyint unsigned".equals(type)) {
			return "Integer";
		} else {
			throw new RuntimeException("--未知的数据库类型 " + type + "--");
		}
	}

	public static List<String> executeSelectSql(String sql, String tableName, String columnName) {
		DataSource dataSource = initDb();
		Connection connection = null;
		if(DatabaseBattery.connection == null) {
			try {
				connection = dataSource.getConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DatabaseBattery.connection = connection;
		}else {
			connection = DatabaseBattery.connection;
		}
		List<String> query = null;
		try {
			query = SqlExecutor.query(connection, sql, new StringListHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return query;
	}
	
	public static DataSource initDb() {
        DataSource dataSource = null;
        Boolean flag = DatabaseBattery.flag;
        File currentFile = new File(".");
        if(flag == null) {
            flag = false;
            DatabaseBattery.flag = false;
            currentFile = new File(".");
            File[] listFiles = currentFile.listFiles();
            if(listFiles != null && listFiles.length != 0) {
                for (File file : listFiles) {
                    if(file != null) {
                        if(file.getName().endsWith(".jar")) {
                            flag = true;
                            DatabaseBattery.flag = true;
                        }
                    }
                }
            }
        }
        if(flag) {
            File parentFile = new File(currentFile, "db.setting");
            Setting setting = new Setting(parentFile, StandardCharsets.UTF_8, false);
            DSFactory factory = DSFactory.create(setting);
            dataSource = factory.getDataSource();
        }else {
            dataSource = DSFactory.get();
        }
        return dataSource;
    }

}
